﻿using System;
using System.Data;
using System.IO;
using System.Reflection;
using MySql.Data.MySqlClient;

namespace Kiosk
{
    /// <summary>
    /// Class used to access the database.
    /// </summary>
    public class DBWrapper 
    {
        /// <summary>
        /// Connection string to the database.
        /// </summary>
        private string sConnectionString;
        /// <summary>
        /// Constructor that sets the connection string to the database
        /// </summary>
        public DBWrapper()
        {
            //sConnectionString = @"server=localhost;user id=root;password=Smi123;database=clearfield_high;pooling=true;";
           // sConnectionString = @"server=www.freesql.org;user id=CHSgroup;password=clearfield;database=clearfield_high;pooling=true;";
            //sConnectionString = @"server=localhost;user id=chsgroup;password=clearfield;database=clearfieldhigh;pooling=true;";
            //sConnectionString = @"server=db4free.net;user id=chsgroup;password='clearfield';database=clearfieldhigh;pooling=true;";
            //@"server=88.198.43.24;user id=chsgroup;password=clearfield;database=clearfieldhigh;pooling=true;";

            try
            {
                string[] dbConfig = System.IO.File.ReadAllLines(@"config/dbconfig.txt");
                this.sConnectionString = dbConfig[0];
            }
            catch (Exception ex)
            {
                //throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
                System.Windows.Forms.MessageBox.Show("Missing or malformed dbconfig file");
            }
        }
        /// <summary>
        /// This method takes an SQL statment that is passed in and executes it.  The resulting values
        /// are returned in a DataSet.  The number of rows returned from the query will be put into
        /// the reference parameter iRetVal.
        /// </summary>
        /// <param name="sSQL">The SQL statement to be executed.</param>
        /// <param name="iRetVal">Reference parameter that returns the number of selected rows.</param>
        /// <returns>Returns a DataSet that contains the data from the SQL statement.</returns>
        public DataSet ExecuteSQLStatement(string sSQL, ref int iRetVal)
        {
            try
            {
                //Create a new DataSet
                DataSet ds = new DataSet();

                using (MySqlConnection conn = new MySqlConnection(sConnectionString))
                {
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter())
                    {

                        //Open the connection to the database
                        conn.Open();

                        //Add the information for the SelectCommand using the SQL statement and the connection object
                        adapter.SelectCommand = new MySqlCommand(sSQL, conn);
                        adapter.SelectCommand.CommandTimeout = 0;

                        //Fill up the DataSet with data
                        adapter.Fill(ds);
                        conn.Close();
                    }
                }

                //Set the number of values returned
                iRetVal = ds.Tables[0].Rows.Count;
                
                //return the DataSet
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
            }

        }

        /// <summary>
        /// This method takes an SQL statment that is passed in and executes it.  The resulting single 
        /// value is returned.
        /// </summary>
        /// <param name="sSQL">The SQL statement to be executed.</param>
        /// <returns>Returns a string from the scalar SQL statement.</returns>
        public string ExecuteScalarSQL(string sSQL)
        {
            try
            {
                //Holds the return value
                object obj;

                using (MySqlConnection conn = new MySqlConnection(sConnectionString))
                {
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter())
                    {

                        //Open the connection to the database
                        conn.Open();

                        //Add the information for the SelectCommand using the SQL statement and the connection object
                        adapter.SelectCommand = new MySqlCommand(sSQL, conn);
                        adapter.SelectCommand.CommandTimeout = 0;

                        //Execute the scalar SQL statement
                        obj = adapter.SelectCommand.ExecuteScalar();
                        conn.Close();
                    }
                }

                //See if the object is null
                if (obj == null)
                {
                    //Return a blank
                    return "";
                }
                else
                {
                    //Return the value
                    return obj.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
            }
        }

        /// <summary>
        /// This method takes an SQL statment that is a non query and executes it.
        /// </summary>
        /// <param name="sSQL">The SQL statement to be executed.</param>
        /// <returns>Returns the number of rows affected by the SQL statement.</returns>
        public int ExecuteNonQuery(string sSQL)
        {
            try
            {
                //Number of rows affected
                int iNumRows;

                using (MySqlConnection conn = new MySqlConnection(sConnectionString))
                {
                    //Open the connection to the database
                    conn.Open();

                    //Add the information for the SelectCommand using the SQL statement and the connection object
                    MySqlCommand cmd = new MySqlCommand(sSQL, conn);
                    cmd.CommandTimeout = 0;

                    //Execute the non query SQL statement
                    iNumRows = cmd.ExecuteNonQuery();

                    conn.Close();
                }

                //return the number of rows affected
                return iNumRows;
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
            }
        }
    }
}